package br.com.seuimovelnamao.dao;

import java.util.Date;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;

import br.com.seuimovelnamao.StatusImovel;
import br.com.seuimovelnamao.Util;
import br.com.seuimovelnamao.model.Bairro;
import br.com.seuimovelnamao.model.Busca;
import br.com.seuimovelnamao.model.Cidade;
import br.com.seuimovelnamao.model.Estado;
import br.com.seuimovelnamao.model.Imovel;
import br.com.seuimovelnamao.model.Usuario;

public class ImovelDAO extends MasterDAO {

	public ImovelDAO(Session session) {
		super(session);
	}

	public Imovel getImovel(Long id){
		//return (Imovel) this.getSession().load(Imovel.class, id);
		Query query = this.getSession().createQuery("SELECT i FROM Imovel AS i WHERE i.id = :id");
		return (Imovel)query.setParameter("id", id).uniqueResult();
	}
	
	public List<Imovel> getImoveisPorUsuario(Usuario usuario){
		
		Query query = this.getSession().createQuery("SELECT i FROM Imovel AS i WHERE usuario.id = :id");
		return query.setParameter("id", usuario.getId()).list();
		
	}
	
	public void remove(Imovel i){
		this.getSession().delete(i);
	}
	
	public List<Imovel> listaTudo(){
		return this.getSession().createCriteria(Imovel.class).list();
	}
	
	public List<Imovel> pagina(int inicio, int quantidade){
		return this.getSession().createCriteria(Imovel.class).setMaxResults(quantidade).setFirstResult(inicio).list();
	}

	public List<Imovel> getImoveisPorLatLong(double northEastLat, double northEastLong, double southWestLat, double southWestLong, String status, String categoria, String intencao) {
		
		String categoriaIntencaoQuery = "";
		if(!"".equals(categoria))
			categoriaIntencaoQuery += " AND i.categoria = '"+categoria+"'";
		if(!"".equals(intencao))
			categoriaIntencaoQuery += " AND i.intencao = '"+intencao+"'";
		
		Query query = this.getSession().createQuery("SELECT i FROM Imovel AS i, ContratacaoPlano AS cp " +
				"WHERE i.lat BETWEEN :latMin AND :latMax" +
				" AND i.lng BETWEEN :lngMin AND :lngMax" +
				" AND i.status = :status" +
				" AND cp.usuario = i.usuario" +
				" AND cp.pago = true" +
				" AND :data BETWEEN cp.inicio AND cp.fim" +
				categoriaIntencaoQuery);
		return query
				.setParameter("latMin", southWestLat)
				.setParameter("latMax", northEastLat)
				.setParameter("lngMin", southWestLong)
				.setParameter("lngMax", northEastLong)
				.setParameter("status", status)
				.setParameter("data", new Date())
				.list();
		
	}
	
	public List<Imovel> getImoveisPorEstado(Estado estado, String status, String categoria, String intencao){
		
		String categoriaIntencaoQuery = "";
		if(!"".equals(categoria))
			categoriaIntencaoQuery += " AND i.categoria = '"+categoria+"'";
		if(!"".equals(intencao))
			categoriaIntencaoQuery += " AND i.intencao = '"+intencao+"'";
		
		Query query = this.getSession().createQuery("SELECT i FROM Imovel AS i, ContratacaoPlano AS cp " +
				" WHERE i.endereco.estado = :estado " +
				" AND i.status = :status " +
				" AND cp.usuario = i.usuario" +
				" AND cp.pago = true" +
				" AND :data BETWEEN cp.inicio AND cp.fim" +
				categoriaIntencaoQuery);
		return query.setParameter("estado", estado)
				.setParameter("status", status)
				.setParameter("data", new Date())
				.list();
		
	}
	
	public List<Imovel> getImoveisPorCidade(Cidade cidade, String status, String categoria, String intencao){
		
		String categoriaIntencaoQuery = "";
		if(!"".equals(categoria))
			categoriaIntencaoQuery += " AND i.categoria = '"+categoria+"'";
		if(!"".equals(intencao))
			categoriaIntencaoQuery += " AND i.intencao = '"+intencao+"'";
		
		Query query = this.getSession().createQuery("SELECT i FROM Imovel AS i, ContratacaoPlano AS cp" +
				" WHERE i.endereco.cidade = :cidade" +
				" AND i.status = :status " +
				" AND cp.usuario = i.usuario" +
				" AND cp.pago = true" +
				" AND :data BETWEEN cp.inicio AND cp.fim" +
				categoriaIntencaoQuery);
		return query.setParameter("cidade", cidade)
				.setParameter("status", status)
				.setParameter("data", new Date())
				.list();
		
	}

	public List<Imovel> getImoveisPorIntencao(String intencao) {
		Query query = this.getSession().createQuery("SELECT i FROM Imovel AS i WHERE i.intencao = :intencao");
		return query.setParameter("intencao", intencao).list();
	}

	public List<Imovel> getImoveisPorCategoria(String categoria) {
		Query query = this.getSession().createQuery("SELECT i FROM Imovel AS i WHERE i.categoria = :categoria");
		return query.setParameter("categoria", categoria).list();
	}

	public List<Imovel> getImoveis(String categoria, Busca busca) {
		
		String categoriaQuery = Util.getCategoriaQuery(categoria);
		
		String sql = (categoriaQuery.equals("")) ? "WHERE" : categoriaQuery + " AND";
		
		String q = "SELECT i FROM Imovel AS i, ContratacaoPlano AS cp " + 
				sql +
				" i.status = '" + StatusImovel.ATIVO + "' " +
				" AND cp.usuario = i.usuario" +
				" AND cp.pago = true" +
				" AND :data BETWEEN cp.inicio AND cp.fim" +
				" ORDER BY CASE " + getQueryBusca(busca);
		
		System.out.println(q);
		
		Query query = this.getSession().createQuery(q);
		if(!"".equals(categoriaQuery))
			query.setParameter("categoria", categoria);
		return query
				.setParameter("data", new Date())
				.list();
	}
	
	public List<Imovel> getImoveis(String categoria, String intencao, Busca busca) {
		
		String categoriaQuery = Util.getCategoriaQuery(categoria);
		
		String sql = (categoriaQuery.equals("")) ? "WHERE" : categoriaQuery + " AND";
		
		Query query = this.getSession().createQuery("SELECT i FROM Imovel AS i, ContratacaoPlano AS cp " + 
			sql + 
			" i.intencao = :intencao" +
			" AND i.status = '" + StatusImovel.ATIVO + "' " +
			" AND cp.usuario = i.usuario" +
			" AND cp.pago = true" +
			" AND :data BETWEEN cp.inicio AND cp.fim" +
			" ORDER BY CASE " + getQueryBusca(busca));
		if(!"".equals(categoriaQuery))
			query.setParameter("categoria", categoria);
		query.setParameter("intencao", intencao);
		query.setParameter("data", new Date());
		return query.list();
	}
	
	public List<Imovel> getImoveis(String categoria, Estado estado, Busca busca) {
		
		String categoriaQuery = Util.getCategoriaQuery(categoria);
		
		String sql = (categoriaQuery.equals("")) ? "WHERE" : categoriaQuery + " AND";
		
		Query query = this.getSession().createQuery("SELECT i FROM Imovel AS i, ContratacaoPlano AS cp " + 
				sql + 
				" i.endereco.estado = :estado" +
				" AND i.status = '" + StatusImovel.ATIVO + "' " +
				" AND cp.usuario = i.usuario" +
				" AND cp.pago = true" +
				" AND :data BETWEEN cp.inicio AND cp.fim" +
				" ORDER BY CASE " + getQueryBusca(busca));
		if(!"".equals(categoriaQuery))
			query.setParameter("categoria", categoria);
		query.setParameter("estado", estado);
		query.setParameter("data", new Date());
		return query.list();
	}
	
	public List<Imovel> getImoveis(String categoria, String intencao, Estado estado, Busca busca) {
		
		String categoriaQuery = Util.getCategoriaQuery(categoria);
		
		String sql = (categoriaQuery.equals("")) ? "WHERE" : categoriaQuery + " AND";
		
		Query query = this.getSession().createQuery("SELECT i FROM Imovel AS i, ContratacaoPlano AS cp " + 
				sql + 
				" i.endereco.estado = :estado" +
				" AND i.intencao = :intencao" +
				" AND i.status = '" + StatusImovel.ATIVO + "' " +
				" AND cp.usuario = i.usuario" +
				" AND cp.pago = true" +
				" AND :data BETWEEN cp.inicio AND cp.fim" +
				" ORDER BY CASE " + getQueryBusca(busca));
		if(!"".equals(categoriaQuery))
			query.setParameter("categoria", categoria);
		query.setParameter("estado", estado);
		query.setParameter("intencao", intencao);
		query.setParameter("data", new Date());
		return query.list();
	}
	
	public List<Imovel> getImoveis(String categoria, Cidade cidade, Busca busca) {
		
		String categoriaQuery = Util.getCategoriaQuery(categoria);
		
		String sql = (categoriaQuery.equals("")) ? "WHERE" : categoriaQuery + " AND";
		
		Query query = this.getSession().createQuery("SELECT i FROM Imovel AS i, ContratacaoPlano AS cp " + 
				sql + 
				" i.endereco.cidade = :cidade" +
				" AND i.status = '" + StatusImovel.ATIVO + "' " +
				" AND cp.usuario = i.usuario" +
				" AND cp.pago = true" +
				" AND :data BETWEEN cp.inicio AND cp.fim" +
				" ORDER BY CASE " + getQueryBusca(busca));
		if(!"".equals(categoriaQuery))
			query.setParameter("categoria", categoria);
		query.setParameter("cidade", cidade);
		query.setParameter("data", new Date());
		return query.list();
	}
	
	public List<Imovel> getImoveis(String categoria, String intencao, Cidade cidade, Busca busca) {
		
		String categoriaQuery = Util.getCategoriaQuery(categoria);
		
		String sql = (categoriaQuery.equals("")) ? "WHERE" : categoriaQuery + " AND";
		
		Query query = this.getSession().createQuery("SELECT i FROM Imovel AS i, ContratacaoPlano AS cp " + 
				sql + 
				" i.endereco.cidade = :cidade" +
				" AND i.intencao = :intencao" +
				" AND i.status = '" + StatusImovel.ATIVO + "' " +
				" AND cp.usuario = i.usuario" +
				" AND cp.pago = true" +
				" AND :data BETWEEN cp.inicio AND cp.fim" +
				" ORDER BY CASE " + getQueryBusca(busca));
		if(!"".equals(categoriaQuery))
			query.setParameter("categoria", categoria);
		query.setParameter("cidade", cidade);
		query.setParameter("intencao", intencao);
		query.setParameter("data", new Date());
		return query.list();
	}
	
	public List<Imovel> getImoveis(String categoria, Bairro bairro, Busca busca) {
		
		String categoriaQuery = Util.getCategoriaQuery(categoria);
		
		String sql = (categoriaQuery.equals("")) ? "WHERE" : categoriaQuery + " AND";
		
		Query query = this.getSession().createQuery("SELECT i FROM Imovel AS i, ContratacaoPlano AS cp " + 
				sql + 
				" i.endereco.bairro = :bairro" +
				" AND i.status = '" + StatusImovel.ATIVO + "' " +
				" AND cp.usuario = i.usuario" +
				" AND cp.pago = true" +
				" AND :data BETWEEN cp.inicio AND cp.fim" +
				" ORDER BY CASE " + getQueryBusca(busca));
		if(!"".equals(categoriaQuery))
			query.setParameter("categoria", categoria);
		query.setParameter("bairro", bairro);
		query.setParameter("data", new Date());
		return query.list();
	}
	
	public List<Imovel> getImoveis(String categoria, String intencao, Bairro bairro, Busca busca) {
		
		String categoriaQuery = Util.getCategoriaQuery(categoria);
		
		String sql = (categoriaQuery.equals("")) ? "WHERE" : categoriaQuery + " AND";
		
		Query query = this.getSession().createQuery("SELECT i FROM Imovel AS i, ContratacaoPlano AS cp " + 
				sql + 
				" i.endereco.bairro = :bairro" +
				" AND i.intencao = :intencao" +
				" AND i.status = '" + StatusImovel.ATIVO + "' " +
				" AND cp.usuario = i.usuario" +
				" AND cp.pago = true" +
				" AND :data BETWEEN cp.inicio AND cp.fim" +
				" ORDER BY CASE " + getQueryBusca(busca));
		if(!"".equals(categoriaQuery))
			query.setParameter("categoria", categoria);
		query.setParameter("bairro", bairro);
		query.setParameter("intencao", intencao);
		query.setParameter("data", new Date());
		return query.list();
	}

	public List<String> getDistinctCategoria(){
		
		Query query = this.getSession().createQuery("SELECT distinct i.categoria FROM Imovel AS i");
		return query.list();
		
	}
	
	public List<String> getDistinctIntencao(){
		
		Query query = this.getSession().createQuery("SELECT distinct i.intencao FROM Imovel AS i");
		return query.list();
		
	}

	public List<String> getDistinctCategoriaIntencao(String categoria) {
		String categoriaQuery = Util.getCategoriaQuery(categoria);
		
		Query query = this.getSession().createQuery("SELECT distinct i.intencao FROM Imovel AS i " + categoriaQuery);
		if(!"".equals(categoriaQuery))
			query.setParameter("categoria", categoria);
		return query.list();
	}
	
	public String getQueryBusca(Busca busca){
		String query = "";
		int ordem = 1;
		
		// banheiros menos importante
		if(busca.getBanheiros() > 0 ){
			query += " WHEN i.banheiros = " + busca.getBanheiros() + " THEN " + ordem++;
			query += " WHEN i.banheiros = " + (busca.getBanheiros() + 1) + " THEN " + ordem++;
			query += " WHEN i.banheiros = " + (busca.getBanheiros() + 2) + " THEN " + ordem++;
			query += " WHEN i.banheiros > " + (busca.getBanheiros() + 2) + " THEN " + ordem++;
			query += " WHEN i.banheiros < " + busca.getBanheiros() + " THEN " + ordem++;
		}
		
		// vagas
		if(busca.getVagasGaragem() > 0 ){
			query += " WHEN i.vagasGaragem = " + busca.getVagasGaragem() + " THEN " + ordem++;
			query += " WHEN i.vagasGaragem = " + (busca.getVagasGaragem() + 1) + " THEN " + ordem++;
			query += " WHEN i.vagasGaragem = " + (busca.getVagasGaragem() + 2) + " THEN " + ordem++;
			query += " WHEN i.vagasGaragem > " + (busca.getVagasGaragem() + 2) + " THEN " + ordem++;
			query += " WHEN i.vagasGaragem < " + busca.getVagasGaragem() + " THEN " + ordem++;
		}
		
		// dormitorios
		if(busca.getDormitorios() > 0 ){
			query += " WHEN i.dormitorios = " + busca.getDormitorios() + " THEN " + ordem++;
			query += " WHEN i.dormitorios = " + (busca.getDormitorios() + 1) + " THEN " + ordem++;
			query += " WHEN i.dormitorios = " + (busca.getDormitorios() + 2) + " THEN " + ordem++;
			query += " WHEN i.dormitorios > " + (busca.getDormitorios() + 2) + " THEN " + ordem++;
			query += " WHEN i.dormitorios < " + busca.getDormitorios() + " THEN " + ordem++;
		}
		
		// area
		if(busca.getArea() > 0 ){
			query += " WHEN i.areaUtil > " + busca.getArea() + " THEN " + ordem++;
			query += " WHEN i.areaUtil < " + busca.getArea() + " THEN " + ordem++;
		}
		
		// preco
		if(busca.getPrecoDe() > -1 && busca.getPrecoAte() == -1){
			query += " WHEN i.preco >= " + busca.getPrecoDe() + " THEN " + ordem++;
		}else if(busca.getPrecoDe() == -1 && busca.getPrecoAte() > -1){
			query += " WHEN i.preco <= " + busca.getPrecoAte() + " THEN " + ordem++;
		}else if(busca.getPrecoDe() > -1 && busca.getPrecoAte() > -1){
			query += " WHEN i.preco BETWEEN " + busca.getPrecoDe() + " AND " + busca.getPrecoAte() + " THEN " + ordem++;
		}
		
		query += " ELSE " + ordem + " END";
		
		return query;
	}
	
}